
package Oracle;

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author odmaestrams
 */
public class OracleMethods {
    
    public static Connection oracleConnection(){
        Connection connection = null;
        try {
            // Load the JDBC driver
            String driverName = "oracle.jdbc.driver.OracleDriver";
            Class.forName(driverName);
            
            // Create a connection to the database
            String serverName = "localhost";
            String portNumber = "1521";
            String sid = "xe";
            String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
            String username = "system";
            String password = "4884875";
            connection = DriverManager.getConnection(url, username, password);
  
        } catch (ClassNotFoundException ex) {
             System.out.println("Could not find the database driver");
             return null;
            
        } catch (SQLException ex) {
            System.out.println(ex);
            System.out.println("Could not connect to the database");
            return null;
        }
        
        return connection;
    }
    
    public static ArrayList<String> todosLosKioscos() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT kiosco_PK,nombre FROM ao_kiosco");
        
        ArrayList<String> kioscosArray = new ArrayList<String>();
        while(rs.next()) { 
            kioscosArray.add(rs.getString("kiosco_PK") + " - " + rs.getString("nombre"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return kioscosArray;
    }
    
    public static ArrayList<String> todosLosServicios() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT servicio_PK,nombre FROM ao_servicio");
        
        ArrayList<String> serviciosArray = new ArrayList<String>();
        while(rs.next()) { 
            serviciosArray.add(rs.getString("servicio_PK") + " - " + rs.getString("nombre"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return serviciosArray;
    }
    
    public static ArrayList<String> todosLosTiposPago() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT formaPago_PK,nombre FROM ao_forma_pago");
        
        ArrayList<String> tiposPagoArray = new ArrayList<String>();
        while(rs.next()) { 
            tiposPagoArray.add(rs.getString("formaPago_PK") + " - " + rs.getString("nombre"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return tiposPagoArray;
    }
    
    public static ArrayList<String> todosLosEmpleados() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT ci_PK,nombre,apellido FROM ao_empleado");
        
        ArrayList<String> empleadosArray = new ArrayList<String>();
        while(rs.next()) { 
            empleadosArray.add(rs.getString("ci_PK") + " - " + rs.getString("nombre") + " " + rs.getString("apellido"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return empleadosArray;
    }
    
    public static ArrayList<String> todosLosMantenimientos() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM ao_mantenimiento");
        
        ArrayList<String> mantenimientosArray = new ArrayList<String>();
        while(rs.next()) { 
            mantenimientosArray.add(rs.getString("mantenimiento_PK") + ";" + rs.getString("tipo") + ";" + 
                    rs.getString("costo_hora_empleado") + ";" + rs.getInt("costo_hora_cliente"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return mantenimientosArray;
    }
    
    public static ArrayList<String> todosLosClientes() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT rif_PK,nombreEmpresa FROM ao_cliente");
        
        ArrayList<String> clientsArray = new ArrayList<String>();
        while(rs.next()) { 
            clientsArray.add(rs.getString("rif_PK") + " - " + rs.getString("nombreEmpresa"));
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return clientsArray;
    }
    
    public static ArrayList<String> todosLosMateriales() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM ao_material");
        
        ArrayList<String> materialesArray = new ArrayList<String>();
        while(rs.next()) {
            materialesArray.add(rs.getString("material_PK") + ".- " + rs.getString("material"));
        }
 
        stmt.close();
        rs.close();
        con.close();
        
        return materialesArray;
    }
    
    public static ArrayList<String> todosLosTamanos() throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM ao_valla");
        
        ArrayList<String> tamanosArray = new ArrayList<String>();
        while(rs.next()) {
            tamanosArray.add(rs.getString("valla_PK") + ".- " + rs.getString("tamano"));
        }
 
        stmt.close();
        rs.close();
        con.close();
        
        return tamanosArray;
    }
    
    public static String buscarCostosMant(String tipoMant) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT costo_hora_empleado,costo_hora_cliente FROM ao_mantenimiento "
                + "WHERE mantenimiento_PK = (SELECT mantenimiento_PK FROM ao_mantenimiento "
                + "WHERE tipo = " + "'" + tipoMant + "'" + ")"); 
        
        String costos = "";
        if(rs.next()) {
            costos = rs.getString("costo_hora_empleado" ) + ";" + rs.getString("costo_hora_cliente");
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return costos;
    }
    
    public static Integer buscarNroTipoMantenimiento(String tipoMant) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT DISTINCT mantenimiento_PK FROM ao_mantenimiento "
                + "WHERE tipo = " + "'" + tipoMant + "'");
        
        Integer nroTipoMant = 0; 
        
        if(rs.next()){
            nroTipoMant = rs.getInt("mantenimiento_PK");
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return nroTipoMant;
    }
    
    public static String buscarEmpleado(String ciEmpleado) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT nombre, apellido, telefono1 FROM ao_empleado WHERE ao_empleado.ci_PK = " + ciEmpleado);
        
        String empleado = "";
        
        while(rs.next()) {
            empleado = empleado + rs.getString("nombre") + ";";
            empleado = empleado + rs.getString("apellido") + ";";
            empleado = empleado + rs.getString("telefono1") + ";";
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return empleado;
    }
    
    public static String buscarCliente(String rifCliente) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT nombreEmpresa, personaContacto, telefono1, telefono2 "
                + "FROM ao_cliente WHERE ao_cliente.rif_PK = " + rifCliente);
        
        String cliente = "";
        
        while(rs.next()) {
            cliente = cliente + rs.getString("nombreEmpresa") + ";";
            cliente = cliente + rs.getString("personaContacto") + ";";
            cliente = cliente + rs.getString("telefono1") + ";";       
            cliente = cliente + rs.getString("telefono2") + ";";
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return cliente;
    }
    
    public static String buscarCostoPedido(String material_PK, String valla_PK) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT valla_material_PK,costo FROM ao_valla_material WHERE "
                + "ao_valla_material.material_PK_FK = " + material_PK + " AND ao_valla_material.valla_PK_FK = " + valla_PK);
        
        String vallaMaterial = "";
        
        while(rs.next()) {
            vallaMaterial = vallaMaterial + rs.getString("valla_material_PK") + ";";
            vallaMaterial = vallaMaterial + rs.getString("costo");
        }
        
        stmt.close();
        rs.close();
        con.close();

        return vallaMaterial;
    }
    
    public static String buscarCaracteristicasPedido(String nroPedido, String rifCliente) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT valla_FK, material_FK FROM ao_detalle_pedido "
                + "WHERE pedido_PK_FK = " + nroPedido + " "
                + "AND rif_PK_FK = " + "'" + rifCliente + "'");
        
        String caracteristicasPedido = "";
        
        if(rs.next()) {
            caracteristicasPedido = rs.getString("valla_FK") + ";" + rs.getString("material_FK");
        }
        
        rs = stmt.executeQuery("SELECT tamano FROM ao_valla "
                + "WHERE valla_PK  = " + rs.getInt(1));
        
        if (rs.next()){
            caracteristicasPedido = caracteristicasPedido + ";" + rs.getString(1);
        }
        
        stmt.close();
        rs.close();
        con.close();

        return caracteristicasPedido;
    }
    
    public static String buscarCostoAlquiler(Integer nroKiosco, String posicion, String formaPago_PK, String servicio_PK) throws SQLException {
        if (posicion.equals("Izquierda"))
            posicion = "COSTO_IZQUIERDA";
        else if (posicion.equals("Derecha"))
            posicion = "COSTO_DERECHA";
        else
            posicion = "COSTO_ATRAS";
    
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT " + posicion + ", ao_forma_pago.descuento, ao_servicio.descuento "
                + "FROM ao_kiosco, ao_forma_pago, ao_servicio WHERE ao_kiosco.kiosco_PK = " + nroKiosco + " "
                + "AND ao_forma_pago.formaPago_PK = " + formaPago_PK + " "
                + "AND ao_servicio.servicio_PK = " + servicio_PK);
        
        String infoCostoAlquiler = "";
        
        if(rs.next()) {
            infoCostoAlquiler = infoCostoAlquiler + rs.getString(1) + ";";
            infoCostoAlquiler = infoCostoAlquiler + rs.getString(2) + ";";
            infoCostoAlquiler = infoCostoAlquiler + rs.getString(3);
        }
  
        stmt.close();
        rs.close();
        con.close();
        
        return infoCostoAlquiler;
    }
    
    public static String buscarPosicionesOcupadas(Integer nroKiosco) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT posicion, fechaFin FROM ao_alquiler "
                + "WHERE ao_alquiler.kiosco_PK_FK = " + nroKiosco);
        
        String posiciones = "";
        
        while(rs.next()){
            posiciones = posiciones + rs.getString("posicion") + ";" + rs.getDate("fechaFin") + ";";
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return posiciones;
    }
    
    public static String buscarKioscosOcupados(Integer nroTipoMant, String fechaC) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT DISTINCT kiosco_PK_FK FROM ao_mant_kiosco "
                + "WHERE fecha_PK = TO_DATE(" + "'" + fechaC + "'," + "'dd/mm/yyyy') "
                + "AND mant_PK_FK = " + nroTipoMant);
        
        String nroKioscos = "";
        
        while(rs.next()){
            nroKioscos = nroKioscos + rs.getString("kiosco_PK_FK") + ";";
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return nroKioscos;
    }
    
    public static String buscarEmpleadosOcupados(String fechaMant) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT ci_PK_FK FROM ao_mant_kiosco "
                + "WHERE fecha_PK = TO_DATE(" + "'" + fechaMant + "'," + "'dd/mm/yyyy')" );
        
        String empleadosOcupados = "";
        
        while(rs.next()){
            empleadosOcupados = empleadosOcupados + rs.getString("ci_PK_FK") + ";";
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return empleadosOcupados;
    }
    
    public static String buscarInfoAlquiler(Integer nroAlquiler, Integer nroKiosco) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT formaPago_PK_FK,servicio_PK_FK,monto FROM ao_alquiler, ao_detalle "
                + "WHERE ao_alquiler.alquiler_PK = " + nroAlquiler + " "
                + "AND ao_detalle.alquiler_FK = " + nroAlquiler);
        
        int nroFormaPago = 0, nroServicio = 0;
        
        if (rs.next()){
            if (nroKiosco == null)
                return (rs.getInt(1) + ";" + rs.getInt(2) + ";" + rs.getInt(3));
 
            nroFormaPago = rs.getInt(1);
            nroServicio = rs.getInt(2);
        }
        
        rs = stmt.executeQuery("SELECT ao_forma_pago.nombre, ao_forma_pago.descuento, ao_servicio.nombre, "
                + "ao_servicio.descuento, ao_kiosco.nombre,ao_detalle.monto "
                + "FROM ao_forma_pago, ao_servicio, ao_kiosco, ao_detalle "
                + "WHERE ao_forma_pago.formaPago_PK = " + nroFormaPago + " "
                + "AND ao_servicio.servicio_PK = " + nroServicio + " "
                + "AND ao_kiosco.kiosco_PK = " + nroKiosco + " "
                + "AND ao_detalle.detalle_PK = (SELECT detalle_PK FROM ao_detalle "
                + "WHERE ao_detalle.alquiler_FK = " + nroAlquiler + ")");
        
        String infoAlquiler = "";
        
        if (rs.next()){
            infoAlquiler = rs.getString(1) + ";" + rs.getString(2) + ";"
                    + rs.getString(3) + ";" + rs.getString(4) + ";"
                    + rs.getString(5) + ";" + rs.getString(6);
        }
        
        //stmt.close();
        //rs.close();
        con.close();
        
        return infoAlquiler;
    }
    
    public static String buscarServicio(String nroServicio) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT nombre, descuento FROM ao_servicio "
                + "WHERE ao_servicio.servicio_PK = " + nroServicio);
        
        String infoServicio = "";
        
        if (rs.next()) {
            infoServicio = rs.getString("nombre") + ";" + rs.getString("descuento");
            
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return infoServicio;
    }
    
    public static String buscarFormaPago(String nroFormaPago) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT nombre, descuento FROM ao_forma_pago "
                + "WHERE ao_forma_pago.formaPago_PK = " + nroFormaPago);
        
        String infoFormaPago = "";
        
        if (rs.next()) {
            infoFormaPago = rs.getString("nombre") + ";" + rs.getString("descuento");
            
        }
        
        stmt.close();
        rs.close();
        con.close();
        
        return infoFormaPago;
    }
    
    public static String buscarKiosco(String nroKiosco) throws SQLException {
        Connection con = OracleMethods.oracleConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT nombre FROM ao_kiosco "
                + "WHERE ao_kiosco.kiosco_PK = " + nroKiosco);
        
        String nombreKiosco = "";
        
        if (rs.next()) {
            nombreKiosco = rs.getString("nombre");     
        }
        stmt.close();
        rs.close();
        con.close();
        
        return nombreKiosco;
    }
}
